package org.firebug.spring.boot.mybatis.repository.mapper;

import org.apache.ibatis.annotations.*;
import org.firebug.spring.boot.mybatis.repository.entity.UserPO;
import org.firebug.spring.boot.mybatis.repository.qo.UserQO;

import java.util.Collection;
import java.util.List;

@Mapper
public interface UserDAO {
    String SQL_WHERE_BY_QO =
            "<if test='name!=null and name!=\"\"'> and name like concat('%',#{name},'%')</if>" + //全模糊匹配
            "<if test='minSalary!=null'> and #{minSalary}&lt;=salary </if>" + //区间匹配min
            "<if test='maxSalary!=null'> and salary&lt;= #{maxSalary}</if>" + //区间匹配max
            "<if test='genders!=null and genders.size&gt;0'>" +  //多枚举值匹配
                " and gender in " +
                "<foreach collection='genders' item='item' index='index' open='(' separator=',' close=')'>" +
                    "#{item}" +
                "</foreach>" +
            "</if>";

    @Insert("insert into t_user (name,salary,gender) values (#{name},#{salary},#{gender})")
    int insert(UserPO po);

    @Options(useGeneratedKeys = true)
    @Insert("insert into t_user (name,salary,gender) values (#{name},#{salary},#{gender})")
    int insertAutoIncrement(UserPO po);

    @Insert("<script>" +
            "insert into t_user (name,salary,gender) values " +
            "<foreach collection='pos' item='item' index='index' separator=','>" +
            "(#{item.name},#{item.salary},#{item.gender})" +
            "</foreach>" +
            "</script>")
    int insertBatch(@Param("pos") Collection<UserPO> pos);

    @Update("<script>" +
            "update t_user set deleted=1 where id in " +
            "<foreach collection='id' item='item' index='index' open='(' separator=',' close=')'>" +
            "${item}" +
            "</foreach>" +
            "</script>")
    <T extends Number> int deleteLogicalById(@Param("id") T... id);

    @Delete("<script>" +
            "delete from t_user where id in " +
            "<foreach collection='id' item='item' index='index' open='(' separator=',' close=')'>" +
            "${item}" +
            "</foreach>" +
            "</script>")
    <T extends Number> int deletePhysicalById(@Param("id") T... id);

    @Update("<script>" +
            "update t_user " +
            "<trim prefix='set' suffixOverrides=','>" +
            "<if test='name!=null and name!=\"\"'>name=#{name},</if>" +
            "<if test='salary!=null'>salary=#{salary},</if>" +
            "<if test='gender!=null'>gender=#{gender},</if>" +
            "</trim>" +
            " where id=${id}" +
            "</script>")
    int updateById(UserPO po);

    @Select("<script>" +
            "select * from t_user where id in " +
            "<foreach collection='id' item='item' index='index' open='(' separator=',' close=')'>" +
            "${item}" +
            "</foreach>" +
            "</script>")
    <T extends Number> List<UserPO> selectById(@Param("id") T... id);

    @Select("<script>" +
            "select coalesce(sum(salary),0) from t_user where deleted=0" + SQL_WHERE_BY_QO +
            "</script>")
    double selectSumSalaryByQO(UserQO qo);

    @Select("<script>" +
            "select count(1) from t_user where deleted=0" + SQL_WHERE_BY_QO +
            "</script>")
    int selectCountByQO(UserQO qo);

    @Select("<script>" +
            "select * from t_user where deleted=0" + SQL_WHERE_BY_QO + " and id>=" +
            "(select id from t_user where deleted=0" + SQL_WHERE_BY_QO + " limit ${offset},1)" +
            " limit 0,${limit}" +
            "</script>")
    List<UserPO> selectByQO(UserQO qo);
}